﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace GenCallDB
{
	class Program
	{
		public const int LicenseStatusIndex = 5;
		public const int LicenseIdIndex = 1;
		public const int ZipCodeIndex = 18;
		public const int CallsignIndex = 4;
		public const int NameIndex = 8;
		public const int LocationIndex = 15;

		public const string LicenseStatusActive = "A";

		static void Main( string[] args )
		{
			string
				input;
			string[]
				prefixes;
			string[]
				values;
			int
				count;
			ActiveLicenses
				activeLicenses = new ActiveLicenses();

			Console.WriteLine( "Enter comma-separated ZIP prefixes to extract:" );
			input = Console.ReadLine();

			if ( !string.IsNullOrWhiteSpace( input ) )
			{
				prefixes = input.Split( ',', ' ' ).Where( p => !string.IsNullOrWhiteSpace( p ) ).ToArray();

				Console.WriteLine( "Opening HD..." );
				try
				{
					using ( StreamReader reader = File.OpenText( "HD.dat" ) )
					{
						count = 0;
						while ( ( input = reader.ReadLine() ) != null )
						{
							if ( !string.IsNullOrWhiteSpace( input ) )
							{
								++count;
								values = input.Split( '|' );
								if ( values[ LicenseStatusIndex ] == LicenseStatusActive )
								{
									activeLicenses[ int.Parse( values[ LicenseIdIndex ] ) ] = true;
								}
							}
						}
					}

					Console.WriteLine( string.Format( "Read {0} entries from HD", count ) );
				}
				catch
				{
					Console.WriteLine( "Error reading from HD" );
					return;
				}

				Console.WriteLine( "Opening EN..." );
				try
				{
					using ( StreamReader reader = File.OpenText( "EN.dat" ) )
					{
						count = 0;
						using ( StreamWriter writer = File.CreateText( "valid_callsigns.sql" ) )
						{
							writer.WriteLine( "TRUNCATE TABLE valid_callsigns;" );

							while ( ( input = reader.ReadLine() ) != null )
							{
								if ( !string.IsNullOrWhiteSpace( input ) )
								{
									values = input.ToUpper().Replace( "'", string.Empty ).Split( '|' );
									if ( activeLicenses[ int.Parse( values[ LicenseIdIndex ] ) ] )
									{
										if ( !string.IsNullOrWhiteSpace( values[ CallsignIndex ] ) && !string.IsNullOrWhiteSpace( values[ NameIndex ] ) )
										{
											foreach ( string item in prefixes )
											{
												if ( values[ ZipCodeIndex ].StartsWith( item ) )
												{
													++count;
													if ( !string.IsNullOrWhiteSpace( values[ LocationIndex ] ) )
													{
														writer.WriteLine( string.Format( "INSERT INTO valid_callsigns ( roster_id, name, location ) VALUES ( '{0}', '{1}', '{2}' );",
															values[ CallsignIndex ], values[ NameIndex ], values[ LocationIndex ] ) );
													}
													else
													{
														writer.WriteLine( string.Format( "INSERT INTO valid_callsigns ( roster_id, name ) VALUES ( '{0}', '{1}' );",
															values[ CallsignIndex ], values[ NameIndex ] ) );
													}
													Console.WriteLine( string.Format( "Wrote {0} ({1}) at {2}", values[ CallsignIndex ], values[ NameIndex ],
														values[ LocationIndex ] ) );
													break;
												}
											}
										}
									}
								}
							}
						}
					}

					Console.WriteLine( string.Format( "Wrote {0} entries to SQL file", count ) );
				}
				catch
				{
					Console.WriteLine( "Error reading from EN or creating SQL file" );
					return;
				}

				Console.WriteLine( "Processing complete, press Enter to exit..." );
				Console.ReadLine();
			}
		}
	}
}
